﻿
CREATE FUNCTION [QIC].[Interface_DEFAULT](@ConfigName VARCHAR(50))
RETURNS TABLE
AS
RETURN
(
	SELECT 
		Interface.SchemaName,
		Interface.InterfaceName,
		Interface.InterfaceType,
		Interface.InterfaceFactory,
		Interface.InterfaceId,
		Interface.SchemaId		
	FROM
	(	
		SELECT
			Interface.SchemaName,
			Interface.InterfaceName,
			Interface.InterfaceType,
			Interface.InterfaceId,
			Interface.SchemaId,
			ISNULL(InterfaceConfig.ThenInterfaceFactory,Interface.InterfaceType) InterfaceFactory,
			ROW_NUMBER() OVER (PARTITION BY Interface.InterfaceName Order by InterfaceConfig.ThenPrio desc) Ranking
		FROM
		(
			SELECT 
				schemas.name SchemaName,
				QIC.InterfaceNameFactory_(Interface.name) InterfaceName,
				Interface.type InterfaceType,
				Interface.object_id InterfaceId,
				Interface.schema_id SchemaId
			FROM sys.objects Interface
			INNER JOIN sys.schemas schemas
				ON schemas.schema_id = Interface.schema_id
			WHERE Interface.type in ('FN','P','IF','TF','V')

		) Interface
		LEFT OUTER JOIN QIC.Config Config
			ON Config.Name = @ConfigName
		LEFT OUTER JOIN QIC.InterfaceConfig InterfaceConfig
			ON (InterfaceConfig.WhenConfigName IS NULL OR InterfaceConfig.WhenConfigName = Config.Name)
			AND (InterfaceConfig.WhenSchemaName IS NULL OR InterfaceConfig.WhenSchemaName = Interface.SchemaName)
			AND (InterfaceConfig.WhenInterfaceName IS NULL OR InterfaceConfig.WhenInterfaceName = Interface.InterfaceName)
			AND (InterfaceConfig.WhenInterfaceType IS NULL OR InterfaceConfig.WhenInterfaceType COLLATE DATABASE_DEFAULT = Interface.InterfaceType COLLATE DATABASE_DEFAULT)
		WHERE Interface.InterfaceName IS NOT NULL
		AND (ISNULL(InterfaceConfig.ThenPrio,0) >= ISNULL(Config.MinPrio,0)) 

	) Interface
	WHERE Interface.Ranking = 1

);